package gc3d.servlet;

import gc3d.database.DBSettings;
import gc3d.database.GridPovDB;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.simple.JSONArray;
import org.json.simple.JSONObject;


public class GetResults extends HttpServlet {

	private static final long serialVersionUID = 1L;

	public void doGet(HttpServletRequest req, HttpServletResponse res)throws ServletException, IOException {
		PrintWriter out = res.getWriter();
		
		GridPovDB db;
		try {
			JSONArray taskList = new JSONArray(); 
			db = new GridPovDB(DBSettings.getDBURL(),DBSettings.getDBuser(),DBSettings.getDBpass());
			ResultSet rs = db.query("SELECT task_id, name, finish_time, video_link,last_frame,frame_link FROM tasks LEFT JOIN(SELECT task_id AS tasks_id2,max(finish_time) AS last_frame FROM jobs GROUP BY task_id) ON task_id = tasks_id2 LEFT JOIN(SELECT task_id AS tasks_id3,finish_time AS this_frame,frame_link FROM jobs) ON tasks_id2 = tasks_id3 AND last_frame = this_frame ORDER BY tasks.start_time DESC, tasks.task_id ASC;");
			String last_task_id = new String("");
			while( rs.next() ){
				JSONObject task = new JSONObject();
				
				// makes sure that the NULL value (without quotes) is avoided.
				String task_id = (rs.getString("task_id") != null) ? rs.getString("task_id") : "null";
				String name = (rs.getString("name") != null) ? rs.getString("name") : "null";
				String finish_time = (rs.getString("finish_time") != null) ? rs.getString("finish_time") : "null";
				String video_link = (rs.getString("video_link") != null) ? rs.getString("video_link") : "null";
				String last_frame = (rs.getString("last_frame") != null) ? rs.getString("last_frame") : "null";
				String frame_link = (rs.getString("frame_link") != null) ? rs.getString("frame_link") : "null";
				
				if(! task_id.equals(last_task_id)){
				
					task.put("task_id", task_id);
					task.put("name", name);
					task.put("finish_time", finish_time);
					task.put("video_link", video_link);
					task.put("last_frame", last_frame);
					task.put("frame_link", frame_link);
					taskList.add(task);
					last_task_id = task_id;
				}
			}
			out.print(taskList);
		} catch (ClassNotFoundException e1) {
			out.println("ClassNotFoundExceptio");
			// TODO Auto-generated catch block
			out.println(e1.getMessage());
		} catch (SQLException e1) {
			out.println("SQLException");
			// TODO Auto-generated catch block
			out.println(e1.getMessage());
		}
	}
}
/*

	SELECT task_id, finish_time, video_link,last_frame,frame_link
	FROM tasks
	LEFT JOIN(
		SELECT task_id AS tasks_id2,max(finish_time) AS last_frame
		FROM jobs
		GROUP BY task_id
	) ON task_id = tasks_id2
	LEFT JOIN(
		SELECT task_id AS tasks_id3,finish_time AS this_frame,frame_link
		FROM jobs
	) ON tasks_id2 = tasks_id3 AND last_frame = this_frame
	ORDER BY tasks.start_time DESC, tasks.task_id ASC;
	
*/